package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Score;
import cn.edu.lingnan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScoreDaoMysqlImpl implements ScoreDao {
    @Override
    public List<Score> findScoreBySid(String _sid) {
        List<Score> list = new ArrayList<>();

        boolean flag =false;
        Connection conn=null;
        PreparedStatement prep=null;
        ResultSet rs=null;


        try {
            conn= DBUtil.getInstance().getConnection();
            prep=conn.prepareStatement("select * from score where sid=?");
            prep.setString(1,_sid);
            rs=prep.executeQuery();
            while (rs.next()) {
                Score score = new Score();
                score.setSid(rs.getString("sid"));
                score.setCid(rs.getString("cid"));
                score.setScore(rs.getInt("score"));
                score.setSflag(rs.getInt("sflag"));
                list.add(score);
            }



        }  catch (SQLException e) {

        }finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }

        return list;

    }

    @Override
    public List<Score> findAllScores() {

            List<Score> list = new ArrayList<>();
            Connection conn = null;
            PreparedStatement prep = null;
            ResultSet rs = null;

            try {
                conn = DBUtil.getInstance().getConnection();
                prep = conn.prepareStatement("SELECT * FROM score");
                rs = prep.executeQuery();

                while (rs.next()) {
                    Score score = new Score();
                    score.setSid(rs.getString("sid"));
                    score.setCid(rs.getString("cid"));
                    score.setScore(rs.getInt("score"));
                    score.setSflag(rs.getInt("sflag"));
                    list.add(score);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.getInstance().close(rs, prep, conn);
            }

            return list;
        }


    @Override
    public int findScoreNumByCid(String _cid) {
        int flag=0;
        Connection conn=null;
        PreparedStatement prep=null;
        ResultSet rs=null;


        try {
            conn= DBUtil.getInstance().getConnection();
            prep=conn.prepareStatement("select count(*) as num from score where cid=?");
            prep.setString(1,_cid);
            rs=prep.executeQuery();
            rs.next();
            flag= Integer.parseInt(rs.getString("num"));



        }  catch (SQLException e) {

        }finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return flag;
    }

    @Override
    public int deleteScoreBySid(String _sid) {
        int flag=0;
        Connection conn=null;
        PreparedStatement prep=null;


        try {
            conn= DBUtil.getInstance().getConnection();
            prep=conn.prepareStatement("delete num from score where sid=?");
            prep.setString(1,_sid);
            prep.executeUpdate();
            flag=1;



        }  catch (SQLException e) {

        }finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }

    @Override
    public int insertScore(Score _score) {
        int flag=0;
        Connection conn=null;
        PreparedStatement prep=null;


        try {
            conn= DBUtil.getInstance().getConnection();
            prep=conn.prepareStatement("insert into score values(?,?,?,?)");
            prep.setString(1, _score.getSid());
            prep.setString(2, _score.getCid());
            prep.setInt(3,_score.getScore());
            prep.setInt(4,_score.getSflag());
            prep.executeUpdate();
            flag=1;



        }  catch (SQLException e) {

        }finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }
}
